package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleServiceImpl implements IGetTotleService {
    @Autowired
    private SqlMapper sqlMapper;

    public String getsqlDepartment(SearchWholeHospitalInfo searchInfo, String classification, String condition, String condition1,String conditionOne,String conditionTwo,int i, List<SearchWholeHospitalInfo> list1) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getUser().getAbbreviation();
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sqlb="";
        String performance="hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())){
                sqlb+="";
            }else{
                sqlb+=" and b.treatment_unit='"+searchInfo.getDimensionSelectionCustomerAll()+"'";
            }
            performance="section_performance";
        }
        String sql = " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status='新客首次' and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and "+condition+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status='新客首次' " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " and "+condition+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"'" +
                " and "+condition1+sqlb+" and b.customer_status='新客首次' group by c.id) t),0.00)  as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status='新客多次' and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and "+condition+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status='新客多次' " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " and "+condition+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"'" +
                " and "+condition1+sqlb+" and b.customer_status='新客多次' group by c.id) t),0.00) as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status in ('新客首次','新客多次') and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and "+condition+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次') " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " and "+condition+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"' " +
                " and "+condition1+sqlb+" " +
                " and b.customer_status in ('新客首次','新客多次') group by c.id) t),0.00) as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status='老客' and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and "+condition+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status='老客' " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " and "+condition+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"'" +
                " and "+condition1+sqlb+" and b.customer_status='老客' group by c.id) t),0.00) as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status in ('新客首次','新客多次','老客') and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and "+condition+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status  in ('新客首次','新客多次','老客') " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " and "+condition+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"' " +
                " and "+condition1+sqlb+" and b.customer_status in ('新客首次','新客多次','老客') group by c.id) t),0.00)  as " +
                " departmentPerformance ," +
                " 0 as tcPerformance  from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getTotleSqlDepartment(SearchWholeHospitalInfo searchInfo) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getUser().getAbbreviation();
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sqlb=" ";
        String performance="hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())){
                sqlb+="";
            }else{
                sqlb+=" and b.treatment_unit='"+searchInfo.getDimensionSelectionCustomerAll()+"'";
            }
            performance="section_performance";
        }
        String sql = " select '合计' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status='新客首次' and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' "+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status='新客首次' " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " "+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"'" +
                " "+sqlb+" and b.customer_status='新客首次' group by c.id) t),0.00)  as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '合计' as rirstClassification," +
                "(select count(1) from(select b.id from "+tableName2+" b where b.customer_status='新客多次' and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' "+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status='新客多次' " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " "+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"'" +
                " "+sqlb+" and b.customer_status='新客多次' group by c.id) t),0.00) as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '合计' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status in ('新客首次','新客多次') and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' "+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次') " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " "+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"' " +
                " "+sqlb+" " +
                " and b.customer_status in ('新客首次','新客多次') group by c.id) t),0.00) as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '合计' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status='老客' and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' "+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status='老客' " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " "+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"'" +
                " "+sqlb+" and b.customer_status='老客' group by c.id) t),0.00) as " +
                "departmentPerformance ," +
                " 0 as tcPerformance  from dual " +
                " union all " +
                "select '合计' as rirstClassification," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.customer_status in ('新客首次','新客多次','老客') and " +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' "+sqlb+" " +
                "group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.id from "+tableName2+" b where b.isdeal_status='已成交' and b.customer_status  in ('新客首次','新客多次','老客') " +
                " and b.arrival_time>='"+searchInfo.getStartTime()+"' and b.arrival_time<='" + searchInfo.getEndTime() + "' " +
                " "+sqlb+" group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t."+performance+") from( " +
                " select c."+performance+" from "+tableName3+" c  " +
                " left join "+tableName2+" b on b.id=c.customer_id " +
                " where c.recharge_time >= '"+searchInfo.getStartTime()+"' and c.recharge_time <= '"+searchInfo.getEndTime()+"' " +
                " "+sqlb+" and b.customer_status in ('新客首次','新客多次','老客') group by c.id) t),0.00)  as " +
                " departmentPerformance ," +
                " 0 as tcPerformance  from dual ";
         return sql;
    }
}